udong market growth plan

Author

우동공구 개발팀 - 이동희

Published

November 30, 2022

import sys
print(sys.executable) # /opt/homebrew/opt/python@3.10/bin/python3.10
/Users/noru/opt/anaconda3/envs/analysis_python_3.10/bin/python
print(sys.path)#
#sys.path.remove('/opt/homebrew/lib/python3.10/site-packages')
# sys.path.append('/opt/homebrew/lib/python3.10/site-packages')
['/Users/noru/Documents/analysis/udong_test', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python310.zip', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10/lib-dynload', '', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10/site-packages']
import datetime
from datetime import datetime
import time
import pandas as pd
from pymongo import MongoClient
from google.cloud import bigquery
from google.oauth2 import service_account
import matplotlib.pyplot as plt
import folium

cd = service_account.Credentials.from_service_account_file(
    '/Users/noru/Documents/google_cloud_composer/administrator_key/woodong-default_compute_service_acount/woodong09-prod-d83693476956.json',
)
bqclient = bigquery.Client(credentials=cd, project='woodong09-prod')

## << DEV >> ##
# mongoClient = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-dev.z9hwj.mongodb.net/?retryWrites=true&w=majority")
## << LIVE >> ##
connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.market
ordersCollection = mongoDb.orders

Datetime

  • one
  • two
  • three
# 날짜 설정 
dateTime = datetime(2022, 11, 5, 9, 0, 0) # 22/11/5(토요일) UTC 기준
# unix timestamp (milliseconds) 설정
unixTimestamp = int(datetime.timestamp(dateTime)*1000)
# convert from [unix timestamp (milliseconds)] to [datetime]
convertDateTime = datetime.fromtimestamp(unixTimestamp/1000).strftime('%Y-%m-%d %H:%M:%S.%f')
print(dateTime)
print(unixTimestamp)
print(convertDateTime)
2022-11-05 09:00:00
1667606400000
2022-11-05 09:00:00.000000
pipeline = [
    {
         "$match": {"order_time":{"$lt": unixTimestamp}} 
     }, 
    {   
         "$project": {"_id":1, "fbuid":1, 
                      "entry_route":1, 
                      "original_product_id":1, "product_id":1, "title": 1,
                      "seller_id":1, 
                      "createdAt":1, "order_time":1, 
                      "payment.method":1, "payment.amount":1, "payment.status":1, 
                      "delivery_method":1, 
                      "pickup":1
                     }
    },
]


ordersDocs = ordersCollection.aggregate(pipeline)
ordersDocsList = []

for doc in ordersDocs:
    # 기본 값을 포함
    _id = str(doc.get("_id", None))
    fbuid = str(doc.get("fbuid", None))
    entry_route = str(doc.get("entry_route", None))
    original_product_id = str(doc.get("original_product_id", None))
    product_id = str(doc.get("product_id", None))
    title = str(doc.get("title", None))
    seller_id = str(doc.get("seller_id", None))
    #createdAt = datetime.strptime(str(doc.get("createdAt", None))[:19], "%Y-%m-%d %H:%M:%S")   
    created_utc = str(doc.get("createdAt", None))[:19]
    order_time = int(doc.get("order_time", None))
    payment_method = str(doc.get("payment", None).get("method", None))
    payment_amount = int(doc.get("payment", None).get("amount", 0))
    payment_status = str(doc.get("payment", None).get("status", None))
    delivery_method = str(doc.get("delivery_method", None))
    
    if doc.get("pickup", None) == None:
        pickup_id = None
    elif doc.get("pickup", None) != None :
        pickup_id = str(doc.get("pickup", None).get("id", None))
    
    if doc.get("pickup", None) == None :
        pickup_lat = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_lat = None   
    elif doc.get("pickup", None).get("location", None).get("lat", None) == None :
        pickup_lat = None    
    elif doc.get("pickup", None).get("location", None).get("lat", None) != None :
        pickup_lat = str(doc.get("pickup", None).get("location", None).get("lat", None))    
    
    if doc.get("pickup", None) == None :
        pickup_long = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_long = None  
    elif doc.get("pickup", None).get("location", None).get("long", None) == None :
        pickup_long = None    
    elif doc.get("pickup", None).get("location", None).get("long", None) != None :
        pickup_long = str(doc.get("pickup", None).get("location", None).get("long", None))    
    
    if doc.get("pickup", None) == None :
        pickup_region_code = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_region_code = None  
    elif doc.get("pickup", None).get("location", None).get("region_code", None) == None :
        pickup_region_code = None    
    elif doc.get("pickup", None).get("location", None).get("region_code", None) != None :
        pickup_region_code = str(doc.get("pickup", None).get("location", None).get("region_code", None))

    if doc.get("pickup", None) == None :
        pickup_region_name = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_region_name = None  
    elif doc.get("pickup", None).get("location", None).get("region_name", None) == None :
        pickup_region_name = None    
    elif doc.get("pickup", None).get("location", None).get("region_name", None) != None :
        pickup_region_name = str(doc.get("pickup", None).get("location", None).get("region_name", None))

    if doc.get("pickup", None) == None :
        pickup_title = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_title = None  
    elif doc.get("pickup", None).get("location", None).get("title", None) == None :
        pickup_title = None    
    elif doc.get("pickup", None).get("location", None).get("title", None) != None :
        pickup_title = str(doc.get("pickup", None).get("location", None).get("title", None))
    
    if doc.get("pickup", None) == None :
        pickup_direct_shop = None
    elif doc.get("pickup", None).get("location", None) == None :
        pickup_direct_shop = None  
    elif doc.get("pickup", None).get("location", None).get("direct_shop", None) == None :
        pickup_direct_shop = None    
    elif doc.get("pickup", None).get("location", None).get("direct_shop", None) != None :
        pickup_direct_shop = str(doc.get("pickup", None).get("location", None).get("direct_shop", None))
    
    ordersDocsList.append({"order_id": _id, "fbuid": fbuid, 
                           "entry_route": entry_route, 
                           "original_product_id": original_product_id, "product_id": product_id, "title": title,
                           "seller_id": seller_id, 
                           "created_utc": created_utc, "order_time": order_time,
                           "payment_method": payment_method, "payment_amount": payment_amount, "payment_status": payment_status, 
                           "delivery_method": delivery_method,
                           "pickup_id": pickup_id, "pickup_lat": pickup_lat, "pickup_long": pickup_long,
                           "pickup_region_code": pickup_region_code, "pickup_region_name": pickup_region_name,
                           "pickup_shop": pickup_title, "is_direct_shop": pickup_direct_shop, 
                          })

ordersDf = pd.DataFrame(ordersDocsList)

Order Head

  • one
  • two
ordersDf.tail(2)
order_id fbuid entry_route original_product_id product_id title seller_id created_utc order_time payment_method payment_amount payment_status delivery_method pickup_id pickup_lat pickup_long pickup_region_code pickup_region_name pickup_shop is_direct_shop
74359 97c88926c7dc naver:30936932 HOME None ddd15f09-ff47-4e5a-b9ba-ab60d65af5cd 너무맛잇는츄러스 리뷰만900개이상! 정상가11900원 공구가9900원!! naver:198406276 2022-11-04 14:18:34 1667571514434 credit_debit_card 23300 completed delivery None None None None None None None
74360 9602e2284ec7 kakao:2223442699 HOME None 0c87f74f-03e9-4ab7-987c-7b9db26b78d3 우리동네 소한마리🐂 - 한우 꽃등심 300g naver:199407471 2022-11-04 14:24:44 1667571884306 credit_debit_card 50000 completed pickup 6b6b5340-c6cd-4ddb-b6b3-33c3ebd54b6a 37.3176661631366 127.08695643701 4146552000 경기도 용인시 수지구 풍덕천동 504-10 풍덕천점 True

hjahaha 이게뭐지?


# connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.region
storesCollection = mongoDb.stores
storesDocs = storesCollection.find()
storesDocsList = []

for doc in storesDocs:
    # 기본 값을 포함
    _id = str(doc.get("_id", None))
    h_code = str(doc.get("h_code", None))
    b_code = str(doc.get("b_code", None))
    name = str(doc.get("name", None))
    
    if doc.get("geo", None) == None :
        coordinates = None
    elif doc.get("geo", None).get("coordinates", None) == None :
        coordinates = None  
    elif doc.get("geo", None).get("coordinates", None) != None :
        coordinates = doc.get("geo", None).get("coordinates", None)

    if doc.get("geo", None) == None :
        coordinates_long = None
    elif doc.get("geo", None).get("coordinates", None) == None :
        coordinates_long = None  
    elif doc.get("geo", None).get("coordinates", None) != None :
        coordinates_long = doc.get("geo", None).get("coordinates", None)[0]

    if doc.get("geo", None) == None :
        coordinates_lat = None
    elif doc.get("geo", None).get("coordinates", None) == None :
        coordinates_lat = None  
    elif doc.get("geo", None).get("coordinates", None) != None :
        coordinates_lat = doc.get("geo", None).get("coordinates", None)[1]
   
    
    storesDocsList.append({"_id": _id, 
                           "pickup_region_code": h_code, "b_code": b_code,
                           "name": name,
                           "coordinates": coordinates,
                           "pickup_long": coordinates_long,
                           "pickup_lat": coordinates_lat,
                          })

storesDf = pd.DataFrame(storesDocsList)
storesDf.head(3)
_id pickup_region_code b_code name coordinates pickup_long pickup_lat
0 61b6ef1db06458d148378cc6 4146552000 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
1 61b6ef86b06458d148378cc8 4113557000 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
2 61b6efe1b06458d148378cce 4146554000 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401 
# connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.user
myinfosCollection = mongoDb.user.myinfos

dateTime = datetime(2022, 11, 5, 9, 0, 0) # 22/11/5(토요일) UTC 기준

pipeline = [
    {
         "$match": {"createdAt":{"$lt": dateTime}} 
     }, 
    {   
         "$project": {"fbuid":1, 
                      "nickname": 1,
                      "createdAt":1, 
                      "locationDistance":1, 
                      "preferredRegion":1,
                     }
    },
    {
        "$sort": {"createdAt": -1}
    },
]


myinfosDocs = myinfosCollection.aggregate(pipeline)
myinfosDocsList = []

for doc in myinfosDocs:
    # 기본 값을 포함
    fbuid = str(doc.get("fbuid", None))
    nickname = str(doc.get("nickname", None))
    createdAt = str(doc.get("createdAt", None))
    locationDistance = int(doc.get("locationDistance", 0))
    
    if doc.get("preferredRegion", None) == None :
        coordinates_long = None
    elif doc.get("preferredRegion", None).get("x", None) == None :
        coordinates_long = None  
    elif doc.get("preferredRegion", None).get("x", None) != None :
        coordinates_long = doc.get("preferredRegion", None).get("x", None)

    if doc.get("preferredRegion", None) == None :
        coordinates_lat = None
    elif doc.get("preferredRegion", None).get("y", None) == None :
        coordinates_lat = None  
    elif doc.get("preferredRegion", None).get("y", None) != None :
        coordinates_lat = doc.get("preferredRegion", None).get("y", None)
        
    myinfosDocsList.append({"user_id": fbuid, 
                            "created_at": createdAt,
                            "nickname": nickname,
                            "locationDistance": locationDistance,
                            "coordinates_long": coordinates_long,
                            "coordinates_lat": coordinates_lat,
                            })

myinfosDf = pd.DataFrame(myinfosDocsList)
myinfosDf.head(3)
user_id created_at nickname locationDistance coordinates_long coordinates_lat
0 kakao:2512739756 2022-11-05 08:32:39.285000 김팀장 3 126.948135 37.489543
1 naver:18825577 2022-11-05 08:25:39.085000 나비잡자 3 127.088757 37.319480
2 kakao:2512725134 2022-11-05 08:22:55.531000 겨울사랑 3 127.110457 37.170548 
ordersDf.shape
(74361, 20)
ordersDf.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 74361 entries, 0 to 74360
Data columns (total 20 columns):
 #   Column               Non-Null Count  Dtype 
---  ------               --------------  ----- 
 0   order_id             74361 non-null  object
 1   fbuid                74361 non-null  object
 2   entry_route          74361 non-null  object
 3   original_product_id  74361 non-null  object
 4   product_id           74361 non-null  object
 5   title                74361 non-null  object
 6   seller_id            74361 non-null  object
 7   created_utc          74361 non-null  object
 8   order_time           74361 non-null  int64 
 9   payment_method       74361 non-null  object
 10  payment_amount       74361 non-null  int64 
 11  payment_status       74361 non-null  object
 12  delivery_method      74361 non-null  object
 13  pickup_id            73241 non-null  object
 14  pickup_lat           42029 non-null  object
 15  pickup_long          42029 non-null  object
 16  pickup_region_code   42429 non-null  object
 17  pickup_region_name   42429 non-null  object
 18  pickup_shop          42429 non-null  object
 19  is_direct_shop       28838 non-null  object
dtypes: int64(2), object(18)
memory usage: 11.3+ MB
ordersDf['payment_amount'].describe()
count    7.436100e+04
mean     1.829529e+04
std      1.972587e+04
min      0.000000e+00
25%      1.000000e+04
50%      1.550000e+04
75%      2.100000e+04
max      1.791000e+06
Name: payment_amount, dtype: float64
## < 픽업상품 구매유저의 지역 데이터 >

# 구매유저 fbuid - 중복 제외
pickupPu = ordersDf[ordersDf['delivery_method'] == 'pickup'].drop_duplicates(['fbuid'], ignore_index = True)
pickupPu = pd.merge(pickupPu['fbuid'],myinfosDf, how='left', left_on='fbuid', right_on='user_id')
pickupPu = pickupPu.dropna(axis=0)
pickupPu.head(3)
fbuid user_id created_at nickname locationDistance coordinates_long coordinates_lat
0 kakao:1839010893 kakao:1839010893 2021-08-06 13:22:24.106000 쩡이삐니 0.0 127.058896 37.209882
1 kakao:1824017425 kakao:1824017425 2021-07-27 07:41:01.754000 픽미픽미아이스현미마을 0.0 127.040779 37.188293
2 naver:19786798 naver:19786798 2021-07-30 13:53:24.547000 쁘띠프루츠용인수원 0.0 127.114018 37.331035 
## < 픽업상품 구매유저의 지역 데이터 >

# 구매유저 fbuid - 중복 제외
deliveryPu = ordersDf[ordersDf['delivery_method'] == 'delivery'].drop_duplicates(['fbuid'], ignore_index = True)
deliveryPu = pd.merge(deliveryPu['fbuid'],myinfosDf, how='left', left_on='fbuid', right_on='user_id')
deliveryPu = deliveryPu.dropna(axis=0)
deliveryPu.head(3)
fbuid user_id created_at nickname locationDistance coordinates_long coordinates_lat
0 naver:17838899 naver:17838899 2021-08-26 17:18:28.139000 cmmyeon 0.0 127.071916 37.206523
1 naver:206973907 naver:206973907 2021-08-03 08:48:28.461000 동탄맛집러 0.0 127.088757 37.319480
2 naver:38108207 naver:38108207 2021-08-03 03:31:49.373000 비치콤보 0.0 127.088757 37.319480 
ordersDf.head(2)
order_id fbuid entry_route original_product_id product_id title seller_id created_utc order_time payment_method payment_amount payment_status delivery_method pickup_id pickup_lat pickup_long pickup_region_code pickup_region_name pickup_shop is_direct_shop
0 1627620368257_naver:23946297 naver:23946297 None None naver:1994074711627614920119 남동공단떡볶이 naver:199407471 2021-07-30 04:46:09 1627620369565 credit_debit_card 6500 completed None 572243dc-5748-4304-9e98-7c255ebd2aa1 None None None None None None
1 1627620558367_kakao:1827574818 kakao:1827574818 None None naver:1994074711627614920119 남동공단떡볶이 naver:199407471 2021-07-30 04:49:17 1627620557489 credit_debit_card 6500 completed None 572243dc-5748-4304-9e98-7c255ebd2aa1 None None None None None None 
# shopSales = ordersDf[ordersDf['payment_status'] == 'completed']

# shopSales['pickup_region_code']
# shopSales = shopSales[shopSales['pickup_region_code'] != None] << 작업 실패..
# shopSales.sort_values(by=['created_utc'], ascending=True).head(2)
shopSales = ordersDf[ordersDf['payment_status'] == 'completed']
ordersCreatedAtUTC = pd.to_datetime(shopSales['created_utc'])
shopSales['create_datetime_kst'] = ordersCreatedAtUTC.dt.tz_localize('UTC').dt.tz_convert('Asia/Seoul')
shopSales['created_yearmonth_kst'] = pd.to_datetime(shopSales['create_datetime_kst']).dt.strftime('%Y-%m')

shopSales = shopSales.groupby(['created_yearmonth_kst', 'pickup_region_code'])['payment_amount'].sum()
shopSales = shopSales.reset_index() # groupby 결과를 그대로 데이터프레임으로 저장
shopSales = shopSales.sort_values(by=['created_yearmonth_kst', 'pickup_region_code'], ascending=False)
shopSales = pd.merge(left = shopSales, right = storesDf, how = "left", on = "pickup_region_code")
shopSales = shopSales.dropna(axis=0)
shopSales
# shopSales#[['created_yearmonth_kst', 'pickup_region_code', 'name', 'payment_amount']]
# .plot(kind='barh', color='royalblue')
# plt.title('매장별 누적 매출')
# plt.show()
/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/4111126378.py:3: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/4111126378.py:4: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
created_yearmonth_kst pickup_region_code payment_amount _id b_code name coordinates pickup_long pickup_lat
0 2022-11 4159061000 2049200 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
1 2022-11 4159058500 2335200 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
2 2022-11 4146554000 2072000 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
3 2022-11 4146552000 9803600 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
4 2022-11 4113557000 3875100 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
5 2022-10 4159061000 8679800 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
6 2022-10 4159058500 10057700 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
7 2022-10 4146554000 7381100 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
8 2022-10 4146552000 40242600 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
9 2022-10 4113557000 15931300 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
10 2022-09 4159061000 7324800 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
11 2022-09 4159058500 8530000 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
12 2022-09 4146554000 6415900 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
13 2022-09 4146552000 35004100 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
14 2022-09 4113557000 15185600 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
15 2022-08 4159061000 8389900 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
16 2022-08 4159058500 10522700 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
17 2022-08 4146554000 9654100 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
18 2022-08 4146552000 47840100 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
19 2022-08 4113557000 17965700 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
20 2022-07 4159061000 7366200 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
21 2022-07 4159058500 8326900 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
22 2022-07 4146554000 10319700 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
23 2022-07 4146552000 50462800 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
24 2022-07 4113557000 18268100 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
25 2022-06 4159061000 2384700 62a81ab567a8fa772735f34a 4159013400 동탄 목동점 [127.124116387163, 37.1827023571775] 127.124116 37.182702
26 2022-06 4159058500 2561900 62a81a4467a8fa772735f349 4159012700 동탄 푸른점 [127.059686888308, 37.2017522037433] 127.059687 37.201752
27 2022-06 4146554000 9240000 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
28 2022-06 4146552000 38255900 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
30 2022-06 4113557000 14915200 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
31 2022-05 4146554000 10689000 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
32 2022-05 4146552000 42943100 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
33 2022-05 4113557000 17967000 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
34 2022-04 4146554000 11721900 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
35 2022-04 4146552000 47662800 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
36 2022-04 4113557000 18885700 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199
37 2022-03 4146554000 5050800 61b6efe1b06458d148378cce 4146510200 죽전점 [127.12501304728, 37.3304009693292] 127.125013 37.330401
38 2022-03 4146552000 21147800 61b6ef1db06458d148378cc6 4146510100 풍덕천점 [127.08695643701, 37.3176661631366] 127.086956 37.317666
39 2022-03 4113557000 8247900 61b6ef86b06458d148378cc8 4113510300 정자점 [127.120136737144, 37.3601992734629] 127.120137 37.360199 
# pandas 데이터프레임.iplot() 함수 호출 전에 로드해야 함

import chart_studio.plotly as py
import cufflinks as cf
cf.go_offline(connected=True)
#cf.help('bar')
a = shopSales[['created_yearmonth_kst', 'name', 'payment_amount']].set_index(['created_yearmonth_kst']).loc['2022-03']['name']
list(a)
['죽전점', '풍덕천점', '정자점']
# https://plotly.com/python/bar-charts/
import plotly.express as px

long_df = px.data.medals_long()
fig = px.bar(shopSales[['name', 'payment_amount', 'created_yearmonth_kst']], x="created_yearmonth_kst", y="payment_amount", color="name", title="매장별 월매출")
fig.show()
shopSales[['payment_amount', 'created_yearmonth_kst']].set_index(['created_yearmonth_kst']).iplot(kind='bar')
shopSalesPivot = shopSales[['name', 'payment_amount', 'created_yearmonth_kst']].pivot(index='created_yearmonth_kst', columns='name', values='payment_amount')
shopSalesPivot.iplot(kind='bar', barmode='stack')
#cf.help('scatter')
shopSalesPivot.iplot(kind='scatter')
ordersDf.groupby(['pickup_region_code', 'pickup_lat', 'pickup_long']).sum()
/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/1578443391.py:1: FutureWarning:

The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.
order_time payment_amount
pickup_region_code pickup_lat pickup_long
4113557000 37.3595260841877 127.120610882303 59511543808874 850000
37.360071243708 127.120130663342 41355509108988 435100
37.3601144883473 127.120135247444 2706508795035858 32395300
37.3601992734629 127.120136737144 9221278212090512 83820400
37.360200986796166 127.12013538532256 2670696300769442 23339500
4128158000 37.64697646971491 126.89495970523106 1654758132159 1000
4146351000 37.2742130834493 127.105503350968 24822788838388 394600
4146552000 37.1827023571775 127.124116387163 1660395244976 27000
37.31719334194898 127.08677741878975 7422033851458611 66664300
37.3176256820579 127.086990009479 347014844305478 3965000
37.3176653507245 127.086958466759 5733238553183279 67125600
37.3176661631366 127.08695643701 24595912376536225 223792400
4146554000 37.3298572055999 127.124320586548 52882666183769 654000
37.3299403230536 127.124108595291 3306138206413 44000
37.3299506737632 127.124119218729 31399214968150 497000
37.33030290087551 127.12462033288092 3136016996224518 33241700
37.3304009693292 127.12501304728 4621208723592732 42934200
4159058500 37.2017522037433 127.059686888308 4915110483465426 45720800
4159059000 37.20657779462817 127.09831215838679 1663208258913 10000
4159061000 37.1827023571775 127.124116387163 4107148811427457 39284000 
userList = myinfosDf[['user_id', 'coordinates_lat', 'coordinates_long']]
userList = userList.dropna(axis=0)
userList.head(2)
# userList.shape
user_id coordinates_lat coordinates_long
0 kakao:2512739756 37.489543 126.948135
1 naver:18825577 37.319480 127.088757 
from folium.plugins import MarkerCluster

# 위도 (보라 초등학교)
latitude = 37.2639233
# 경도
longitude = 127.1072747

m = folium.Map(
    location=[latitude, longitude],
    zoom_start=11.2,
#     width=750,
#     height=500,
#     tiles='cartodbpositron'
)


mrkerCluster = MarkerCluster().add_to(m)  

# 매장별 반경 3km(3000m) 도식 <- 반경이 고정되지 않아서 안쓴다.
# for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
#     folium.CircleMarker([lat, long], popup=name, radius=15, color='#FF580B', fill_color='#FF580B',).add_to(m)

# << 매장 위치 표시 >>
for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
    folium.Marker(location = [lat, long], popup=name, icon=folium.Icon(color='red', icon='star')).add_to(m)

# << 가입 유저 - 위치 표시 >>
for lat, long in zip(userList['coordinates_lat'], userList['coordinates_long']):
    folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)

# << 픽업상품 구매 유저 - 위치 표시 >>
# for lat, long in zip(pickupPu['coordinates_lat'], pickupPu['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)
    
# << 배송상품 구매 유저 - 위치 표시 >>
# for lat, long in zip(deliveryPu['coordinates_lat'], deliveryPu['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)

    
m
Make this Notebook Trusted to load map: File -> Trust Notebook
from folium.plugins import MarkerCluster

# 위도 (보라 초등학교)
latitude = 37.2639233
# 경도
longitude = 127.1072747

m2 = folium.Map(
    location=[latitude, longitude],
    zoom_start=11.2,
#     width=750,
#     height=500,
#     tiles='cartodbpositron'
)


mrkerCluster2 = MarkerCluster().add_to(m2)  

# << 매장 위치 표시 >>
for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
    folium.Marker(location = [lat, long], popup=name, icon=folium.Icon(color='red', icon='star')).add_to(m2)

# << 가입 유저 - 위치 표시 >>
# for lat, long in zip(userList['coordinates_lat'], userList['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster2)

# << 픽업상품 구매 유저 - 위치 표시 >>
for lat, long in zip(pickupPu['coordinates_lat'], pickupPu['coordinates_long']):
    folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster2)
    
# << 배송상품 구매 유저 - 위치 표시 >>
# for lat, long in zip(deliveryPu['coordinates_lat'], deliveryPu['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)

    
m2
Make this Notebook Trusted to load map: File -> Trust Notebook
import ipywidgets as widgets
from ipywidgets.embed import embed_minimal_html

out1 = widgets.Output()
out2 = widgets.Output()

# https://stackoverflow.com/questions/63936011/in-python-notebook-change-the-height-of-a-ipywidget
out1.layout.height = '500px' 
out2.layout.height = '500px'


tab = widgets.Tab(children = [out1, out2])
tab.set_title(0, '가입유저 분포')
tab.set_title(1, '구매유저 분포')
display(tab)


with out1:
    out1.clear_output(wait=True)
    display(m)
        
with out2:
    out2.clear_output(wait=True)
    display(m2)
    
# embed_minimal_html('test.html', views=[tab], title='Widgets export')
# ::: {.panel-tabset}

# ## Plot

# ```{python}```
# from folium.plugins import MarkerCluster

# # 위도 (보라 초등학교)
# latitude = 37.2639233
# # 경도
# longitude = 127.1072747

# m = folium.Map(
#     location=[latitude, longitude],
#     zoom_start=11.2,
# #     width=750,
# #     height=500,
# #     tiles='cartodbpositron'
# )


# mrkerCluster = MarkerCluster().add_to(m)  

# # << 매장 위치 표시 >>
# for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
#     folium.Marker(location = [lat, long], popup=name, icon=folium.Icon(color='red', icon='star')).add_to(m)

# # << 가입 유저 - 위치 표시 >>
# for lat, long in zip(userList['coordinates_lat'], userList['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)

# m
# ```

# ## Data

# ```{python}
# m2

# ```

# :::
# from folium.plugins import MarkerCluster
# from folium import plugins

# # 위도 (보라 초등학교)
# latitude = 37.2639233
# # 경도
# longitude = 127.1072747


# m = folium.Map(
#     location=[latitude, longitude],
#     zoom_start=11.2,
# #     width=750,
# #     height=500,
# #     tiles='cartodbpositron'
# )


# #그룹 만들기
# fg = folium.FeatureGroup(name='groups')
# m.add_child(fg)

# g1 = plugins.FeatureGroupSubGroup(fg, 'group1')
# m.add_child(g1)

# g2 = plugins.FeatureGroupSubGroup(fg, 'group2')
# m.add_child(g2)

# folium.LayerControl(collapsed=False).add_to(m)


# # << 매장 위치 표시 >>
# for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
#     folium.Marker(location = [lat, long], popup=name, icon=folium.Icon(color='red', icon='star')).add_to(m)


# # 매장별 반경 3km(3000m) 도식
# # for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
# #     folium.CircleMarker([lat, long], popup=name, radius=15, color='#FF580B', fill_color='#FF580B',).add_to(m)



# # << 유저 위치 표시 >>
# for lat, long in zip(userList['coordinates_lat'], userList['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(g1)

# # << 유저 위치 표시 >>
# for lat, long in zip(pickupPu['coordinates_lat'], pickupPu['coordinates_long']):
#     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(g2)
    
    
    
# # coords = shopSales[['pickup_lat', 'pickup_long']]
# # for lat, long in zip(coords['pickup_lat'], coords['pickup_long']):
# #     folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)


    
    
# m
                                                 
    
# !ls
# !jupyter nbconvert --to html --no-input Untitled.ipynb
#pip install mljar-mercury
# mercury watch Untitled.ipynb
# quarto preview Untitled.ipynb